package com.ugoodtech.mdcc.web.controller;

import com.ugoodtech.mdcc.core.Constants;
import com.ugoodtech.mdcc.core.LogConstants;
import com.ugoodtech.mdcc.core.domain.QuestionAnswerOption;
import com.ugoodtech.mdcc.core.domain.User;
import com.ugoodtech.mdcc.core.domain.VstarUserKpiReport.VstarUserKpiReport;
import com.ugoodtech.mdcc.core.domain.VstarUserKpiReport.VstarUserKpiReport_Complete;
import com.ugoodtech.mdcc.core.domain.VstarUserKpiReport.VstarUserKpiReport_NeedFollowUp;
import com.ugoodtech.mdcc.core.domain.VstarUserKpiReport.VstarUserKpiReport_Plan;
import com.ugoodtech.mdcc.core.repository.QuestionAnswerOptionRepository;
import com.ugoodtech.mdcc.core.repository.UserRepository;
import com.ugoodtech.mdcc.core.service.CoreWeekServiceReportService;
import com.ugoodtech.mdcc.core.utils.DateUtil;
import com.ugoodtech.mdcc.web.dto.JsonResponse;
import com.ugoodtech.mdcc.web.service.WeekServiceReportService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Pageable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.io.FileOutputStream;
import java.util.*;

@Api(description = "导出常规服务报告Excel")
@RestController
@RequestMapping("/exportServiceReport")
public class ExportServiceReportController extends BaseController {

    @Autowired
    private WeekServiceReportService weekServiceReportService;

    @Autowired
    private QuestionAnswerOptionRepository questionAnswerOptionRepository;

    @Autowired
    private CoreWeekServiceReportService coreWeekServiceReportService;

    @Autowired
    private UserRepository userRepository;

    @ApiOperation("导出常规报告EXCEL")
    @RequestMapping(value = "getKpiResponseOutExcel", method = RequestMethod.POST)
    public JsonResponse getKpiResponseOutExcel(HttpServletRequest request,
                                               @ApiParam("登陆用户ID") @RequestParam(value = "loginUserId") Long loginUserId,
                                               @ApiParam("省份") @RequestParam(value = "provinces", required = false) String provinces,
                                               @ApiParam("品牌") @RequestParam(value = "brandname", required = false) String brandname,
                                               @ApiParam("维修站类型") @RequestParam(value = "category", required = false) String category,
//                                      @ApiParam("维修站名称") @RequestParam(value = "name", required = false) String name,
                                               @ApiParam("服务开始时间") @RequestParam(value = "serviceStartTime", required = false) Long serviceStartTime,
                                               @ApiParam("服务结束时间") @RequestParam(value = "serviceEndTime", required = false) Long serviceEndTime,
                                               @ApiParam("开始时间") @RequestParam(value = "startTime", required = false) Long startTime,
                                               @ApiParam("结束时间") @RequestParam(value = "endTime", required = false) Long endTime,
                                               @ApiParam("服务项目") @RequestParam(value = "serviceType", required = false) Long serviceType,
//                                      @ApiParam("服务次数") @RequestParam(value = "serviceNum", required = false) Integer serviceNum,
//                                      @ApiParam("技术人员姓名") @RequestParam(value = "userName", required = false) String userName
                                               @ApiParam("技术人员姓名") @RequestParam(value = "userName", required = false) String userName,
                                               @ApiParam("维修站ID") @RequestParam(value = "stationName", required = false) String stationName,
                                               @ApiParam("客户状态ID") @RequestParam(value = "customerStat", required = false) Long customerStat,
                                               @ApiParam("客户类型ID") @RequestParam(value = "customerType", required = false) Long customerType,
                                               @ApiParam("油漆品牌ID") @RequestParam(value = "paintBrand", required = false) String patintBrand,
                                               @ApiParam("品牌定位ID") @RequestParam(value = "productPosition", required = false) String productPosition,
                                               @ApiParam("维修站编号") @RequestParam(value = "stationNum", required = false) String stationNum,
                                               @ApiParam("是否有待跟进") @RequestParam(value = "isFollowUp", required = false) Boolean isFollowUp,
                                               @ApiParam("部门ID") @RequestParam(value = "departmentId", required = false) Long departmentId,
                                               @ApiParam("是否填写满意度报告") @RequestParam(value = "isCustomer", required = false) Boolean isCustomer,
                                               @ApiParam("满意度打分") @RequestParam(value = "score", required = false) String score,
                                               Pageable pageable) throws Exception {
//        isUserLogin();
        User clientUser = userRepository.findOne(loginUserId);
        com.ugoodtech.mdcc.core.dto.JsonResponse jsonResponse = coreWeekServiceReportService.statWeekReport(clientUser, userName, stationName, provinces, brandname, category, serviceStartTime, serviceEndTime, isFollowUp, isCustomer, score, serviceType,
                startTime, endTime, departmentId);
        VstarUserKpiReport vstarUserKpiReport = (VstarUserKpiReport) jsonResponse.getData();
        String templetPath = request.getSession().getServletContext().getRealPath("");
        String fileName = "漆星服务常规报告.xlsx";
        String sheetName = "常规报告";
        XSSFWorkbook wb = getServiceChartExcel(sheetName, null, vstarUserKpiReport);
        FileOutputStream outputStream = new FileOutputStream(new File(templetPath + "/export/" + fileName));
        wb.write(outputStream);
        outputStream.close();
        //        导出日志============================================
        try {
            Map<String, Object> searchParams = new LinkedHashMap<>();
            searchParams.put("省份", provinces);
            searchParams.put("品牌", brandname);
            searchParams.put("维修站类型", category);
            if (null != serviceStartTime) {
                searchParams.put("服务开始时间", com.ugoodtech.mdcc.core.utils.DateUtil.formatDate(new Date(serviceStartTime), Constants.DATE_FORMAT));
            }
            if (null != serviceEndTime) {
                searchParams.put("服务结束时间", DateUtil.formatDate(new Date(serviceEndTime), Constants.DATE_FORMAT));
            }
            if (null != startTime) {
                searchParams.put("开始时间", com.ugoodtech.mdcc.core.utils.DateUtil.formatDate(new Date(startTime), Constants.DATE_FORMAT));
            }
            if (null != endTime) {
                searchParams.put("结束时间", DateUtil.formatDate(new Date(endTime), Constants.DATE_FORMAT));
            }
            if (null != serviceType) {
                QuestionAnswerOption questionAnswerOption = questionAnswerOptionRepository.findOne(serviceType);
                searchParams.put("服务项目", questionAnswerOption.getName());
            }
            searchParams.put("技术人员姓名", userName);
            if (null != customerStat) {
                QuestionAnswerOption questionAnswerOption = questionAnswerOptionRepository.findOne(customerStat);
                searchParams.put("客户状态", questionAnswerOption.getName());
            }
            if (null != customerType) {
                QuestionAnswerOption questionAnswerOption = questionAnswerOptionRepository.findOne(customerType);
                searchParams.put("客户类型", questionAnswerOption.getName());
            }
            if (null != patintBrand) {
                QuestionAnswerOption questionAnswerOption = questionAnswerOptionRepository.findOne(Long.parseLong(patintBrand));
                searchParams.put("油漆品牌", questionAnswerOption.getName());
            }
            if (null != productPosition) {
                QuestionAnswerOption questionAnswerOption = questionAnswerOptionRepository.findOne(Long.parseLong(productPosition));
                searchParams.put("品牌定位", questionAnswerOption.getName());
            }
            searchParams.put("维修站编号", stationNum);
            searchParams.put("维修站名称", stationName);
            if (null != isFollowUp) {
                if (isFollowUp) {
                    searchParams.put("是否有待跟进", "是");
                } else {
                    searchParams.put("是否有待跟进", "否");
                }
            }
            String desc = "筛选了:";
            for (String key : searchParams.keySet()) {
                if (null != searchParams.get(key) && !StringUtils.isEmpty(searchParams.get(key).toString())) {
                    desc += (key + "=>" + searchParams.get(key) + " ,");
                }
            }
            if ("筛选了:".equals(desc)) {
                desc = "";
            }
            Map<String, Object> data = new HashMap<>();
            data.put("str", desc);
            userLogService.addUserLog(this.getCurrentUser(), null,
                    LogConstants.operate_type_send_export,
                    LogConstants.business_type_regular_report, null, data);
        } catch (Exception e) {
            e.printStackTrace();
        }

        String filePath = Constants.v_starServiceUrl + "/export/" + fileName;
        return JsonResponse.successResponseWithData(filePath);
    }


    private static String[] completeTitle = {"日期", "工作人员", "所属省份", "客户名称", "维修站类型", "具体品牌", "客户代码", "客户状态", "客户类型", "油漆品牌", "品牌定位", "服务商", "重点工作分类", "服务自我评估"};

    private static String[] needFolllowUpTitle = {"日期", "工作人员", "所属省份", "客户名称", "维修站类型", "具体品牌", "客户代码", "客户状态", "客户类型", "油漆品牌", "品牌定位", "服务商", "重点工作分类", "服务自我评估", "异常情况"};

    private static String[] planTitle = {"日期", "工作人员", "所属省份", "客户名称", "维修站类型", "具体品牌", "客户代码", "客户状态", "客户类型", "油漆品牌", "品牌定位", "服务商", "工作内容", "服务自我评价", "具体品牌"};

    private XSSFWorkbook getServiceChartExcel(String sheetName, XSSFWorkbook workbook, VstarUserKpiReport vstarUserKpiRepor) {
        //已完成
        List<VstarUserKpiReport_Complete> vstarUserKpiReport_complete = vstarUserKpiRepor.getVstarUserKpiReport_complete();
        //没有完成，需后续跟进 (工作计划)
        List<VstarUserKpiReport_NeedFollowUp> vstarUserKpiReport_needFollowUp = vstarUserKpiRepor.getVstarUserKpiReport_needFollowUp();
        //计划中
        List<VstarUserKpiReport_Plan> vstarUserKpiReport_Plan = vstarUserKpiRepor.getVstarUserKpiReport_Plan();

        // 第一步，创建一个XSSFWorkbook，对应一个Excel文件
        if (workbook == null) {
            workbook = new XSSFWorkbook();
        }
        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        XSSFSheet sheet = workbook.createSheet("报告详情统计");

        for (int i = 0; i < completeTitle.length; i++) {
            sheet.setColumnWidth(i, 30 * 256);
        }

        XSSFFont titleFont = workbook.createFont();
        titleFont.setFontName("微软雅黑");
        titleFont.setFontHeightInPoints((short) 10);
        titleFont.setBold(true);
        XSSFFont bodyFont = workbook.createFont();
        bodyFont.setFontName("微软雅黑");
        bodyFont.setFontHeightInPoints((short) 10);

        XSSFCellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        XSSFColor titleColor = new XSSFColor(new java.awt.Color(68, 114, 196));
        titleStyle.setFillForegroundColor(titleColor);
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        titleStyle.setBorderLeft(BorderStyle.THIN);
        titleStyle.setLeftBorderColor(titleColor);
        titleStyle.setBorderRight(BorderStyle.THIN);
        titleStyle.setRightBorderColor(titleColor);
        titleStyle.setBorderTop(BorderStyle.THIN);
        titleStyle.setTopBorderColor(titleColor);
        titleStyle.setFont(titleFont);

        XSSFCellStyle bodyStyle = workbook.createCellStyle();
        bodyStyle.setAlignment(HorizontalAlignment.CENTER);
        bodyStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        XSSFColor bodyColor = new XSSFColor(new java.awt.Color(180, 198, 231));
        bodyStyle.setFillForegroundColor(bodyColor);
        bodyStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        bodyStyle.setBorderLeft(BorderStyle.THIN);
        bodyStyle.setLeftBorderColor(bodyColor);
        bodyStyle.setBorderRight(BorderStyle.THIN);
        bodyStyle.setRightBorderColor(bodyColor);
        bodyStyle.setBorderTop(BorderStyle.THIN);
        bodyStyle.setTopBorderColor(bodyColor);
        bodyStyle.setFont(bodyFont);
        bodyStyle.setWrapText(true);

        XSSFCellStyle bodyStyle2 = workbook.createCellStyle();
        bodyStyle2.setAlignment(HorizontalAlignment.CENTER);
        bodyStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
        XSSFColor bodyColor2 = new XSSFColor(new java.awt.Color(217, 225, 242));
        bodyStyle2.setFillForegroundColor(bodyColor2);
        bodyStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        bodyStyle2.setBorderLeft(BorderStyle.THIN);
        bodyStyle2.setLeftBorderColor(bodyColor2);
        bodyStyle2.setBorderRight(BorderStyle.THIN);
        bodyStyle2.setRightBorderColor(bodyColor2);
        bodyStyle2.setBorderTop(BorderStyle.THIN);
        bodyStyle2.setTopBorderColor(bodyColor2);
        bodyStyle2.setFont(bodyFont);
        bodyStyle2.setWrapText(true);

        //创建标题
        XSSFRow titleRow = sheet.createRow(0);
        XSSFCell cell = null;
        for (int i = 0; i < completeTitle.length; i++) {
            cell = titleRow.createCell(i);
            cell.setCellValue(completeTitle[i]);
            cell.setCellStyle(titleStyle);
        }

        XSSFCell sheet1Cell2 = null;
        for (int i = 0; i < vstarUserKpiReport_complete.size(); i++) {
            XSSFRow valueRow = sheet.createRow(i + 1);
            //日期
            sheet1Cell2 = valueRow.createCell(0);
            if (i % 2 == 0) {
                sheet1Cell2.setCellStyle(bodyStyle);
            } else {
                sheet1Cell2.setCellStyle(bodyStyle2);
            }
            sheet1Cell2.setCellValue(vstarUserKpiReport_complete.get(i).getTime());
            //工作人员
            sheet1Cell2 = valueRow.createCell(1);
            if (i % 2 == 0) {
                sheet1Cell2.setCellStyle(bodyStyle);
            } else {
                sheet1Cell2.setCellStyle(bodyStyle2);
            }
            sheet1Cell2.setCellValue(vstarUserKpiReport_complete.get(i).getWorkUser());
            //所属省份
            sheet1Cell2 = valueRow.createCell(2);
            if (i % 2 == 0) {
                sheet1Cell2.setCellStyle(bodyStyle);
            } else {
                sheet1Cell2.setCellStyle(bodyStyle2);
            }
            sheet1Cell2.setCellValue(vstarUserKpiReport_complete.get(i).getProvince());
            //客户名称
            sheet1Cell2 = valueRow.createCell(3);
            if (i % 2 == 0) {
                sheet1Cell2.setCellStyle(bodyStyle);
            } else {
                sheet1Cell2.setCellStyle(bodyStyle2);
            }
            sheet1Cell2.setCellValue(vstarUserKpiReport_complete.get(i).getCustomerName());
            //维修站类型
            sheet1Cell2 = valueRow.createCell(4);
            if (i % 2 == 0) {
                sheet1Cell2.setCellStyle(bodyStyle);
            } else {
                sheet1Cell2.setCellStyle(bodyStyle2);
            }
            sheet1Cell2.setCellValue(vstarUserKpiReport_complete.get(i).getCategory());
            //具体品牌
            sheet1Cell2 = valueRow.createCell(5);
            if (i % 2 == 0) {
                sheet1Cell2.setCellStyle(bodyStyle);
            } else {
                sheet1Cell2.setCellStyle(bodyStyle2);
            }
            sheet1Cell2.setCellValue(vstarUserKpiReport_complete.get(i).getBrand());
            //客户代码
            sheet1Cell2 = valueRow.createCell(6);
            if (i % 2 == 0) {
                sheet1Cell2.setCellStyle(bodyStyle);
            } else {
                sheet1Cell2.setCellStyle(bodyStyle2);
            }
            sheet1Cell2.setCellValue(vstarUserKpiReport_complete.get(i).getStationNum());
            //客户状态
            sheet1Cell2 = valueRow.createCell(7);
            if (i % 2 == 0) {
                sheet1Cell2.setCellStyle(bodyStyle);
            } else {
                sheet1Cell2.setCellStyle(bodyStyle2);
            }
            sheet1Cell2.setCellValue(vstarUserKpiReport_complete.get(i).getCustomerStat());
            //客户类型
            sheet1Cell2 = valueRow.createCell(8);
            if (i % 2 == 0) {
                sheet1Cell2.setCellStyle(bodyStyle);
            } else {
                sheet1Cell2.setCellStyle(bodyStyle2);
            }
            sheet1Cell2.setCellValue(vstarUserKpiReport_complete.get(i).getCustomerType());
            //油漆品牌
            sheet1Cell2 = valueRow.createCell(9);
            if (i % 2 == 0) {
                sheet1Cell2.setCellStyle(bodyStyle);
            } else {
                sheet1Cell2.setCellStyle(bodyStyle2);
            }
            sheet1Cell2.setCellValue(vstarUserKpiReport_complete.get(i).getPaintBrand());
            //品牌定位
            sheet1Cell2 = valueRow.createCell(10);
            if (i % 2 == 0) {
                sheet1Cell2.setCellStyle(bodyStyle);
            } else {
                sheet1Cell2.setCellStyle(bodyStyle2);
            }
            sheet1Cell2.setCellValue(vstarUserKpiReport_complete.get(i).getProduct_position());
            //服务商
            sheet1Cell2 = valueRow.createCell(11);
            if (i % 2 == 0) {
                sheet1Cell2.setCellStyle(bodyStyle);
            } else {
                sheet1Cell2.setCellStyle(bodyStyle2);
            }
            sheet1Cell2.setCellValue(vstarUserKpiReport_complete.get(i).getServiceCompany());
            //重点工作分类
            sheet1Cell2 = valueRow.createCell(12);
            if (i % 2 == 0) {
                sheet1Cell2.setCellStyle(bodyStyle);
            } else {
                sheet1Cell2.setCellStyle(bodyStyle2);
            }
            sheet1Cell2.setCellValue(vstarUserKpiReport_complete.get(i).getWorkType());
            //服务自我评估
            sheet1Cell2 = valueRow.createCell(13);
            if (i % 2 == 0) {
                sheet1Cell2.setCellStyle(bodyStyle);
            } else {
                sheet1Cell2.setCellStyle(bodyStyle2);
            }
            sheet1Cell2.setCellValue(vstarUserKpiReport_complete.get(i).getServicePoint());

        }

        /**
         * 第二个sheet
         */
        XSSFSheet sheet1 = workbook.createSheet("异常报告");

        for (int i = 0; i < needFolllowUpTitle.length; i++) {
            sheet1.setColumnWidth(i, 30 * 256);
        }
        //创建标题
        XSSFRow titleRow2 = sheet1.createRow(0);
        XSSFCell cell2 = null;
        for (int i = 0; i < needFolllowUpTitle.length; i++) {
            cell2 = titleRow2.createCell(i);
            cell2.setCellValue(needFolllowUpTitle[i]);
            cell2.setCellStyle(titleStyle);
        }
        XSSFCell sheet1Cell3 = null;
        for (int i = 0; i < vstarUserKpiReport_needFollowUp.size(); i++) {
            titleRow2 = sheet1.createRow(i + 1);
            //日期
            sheet1Cell3 = titleRow2.createCell(0);
            if (i % 2 == 0) {
                sheet1Cell3.setCellStyle(bodyStyle);
            } else {
                sheet1Cell3.setCellStyle(bodyStyle2);
            }
            sheet1Cell3.setCellValue(vstarUserKpiReport_needFollowUp.get(i).getDateTime());
            //工作人员
            sheet1Cell3 = titleRow2.createCell(1);
            if (i % 2 == 0) {
                sheet1Cell3.setCellStyle(bodyStyle);
            } else {
                sheet1Cell3.setCellStyle(bodyStyle2);
            }
            sheet1Cell3.setCellValue(vstarUserKpiReport_needFollowUp.get(i).getWorkUser());
            //所属省份
            sheet1Cell3 = titleRow2.createCell(2);
            if (i % 2 == 0) {
                sheet1Cell3.setCellStyle(bodyStyle);
            } else {
                sheet1Cell3.setCellStyle(bodyStyle2);
            }
            sheet1Cell3.setCellValue(vstarUserKpiReport_needFollowUp.get(i).getProvince());
            //客户名称
            sheet1Cell3 = titleRow2.createCell(3);
            if (i % 2 == 0) {
                sheet1Cell3.setCellStyle(bodyStyle);
            } else {
                sheet1Cell3.setCellStyle(bodyStyle2);
            }
            sheet1Cell3.setCellValue(vstarUserKpiReport_needFollowUp.get(i).getCuntomerName());
            //维修站类型
            sheet1Cell3 = titleRow2.createCell(4);
            if (i % 2 == 0) {
                sheet1Cell3.setCellStyle(bodyStyle);
            } else {
                sheet1Cell3.setCellStyle(bodyStyle2);
            }
            sheet1Cell3.setCellValue(vstarUserKpiReport_needFollowUp.get(i).getCategory());
            //具体品牌
            sheet1Cell3 = titleRow2.createCell(5);
            if (i % 2 == 0) {
                sheet1Cell3.setCellStyle(bodyStyle);
            } else {
                sheet1Cell3.setCellStyle(bodyStyle2);
            }
            sheet1Cell3.setCellValue(vstarUserKpiReport_needFollowUp.get(i).getBrand());
            //客户代码
            sheet1Cell3 = titleRow2.createCell(6);
            if (i % 2 == 0) {
                sheet1Cell3.setCellStyle(bodyStyle);
            } else {
                sheet1Cell3.setCellStyle(bodyStyle2);
            }
            sheet1Cell3.setCellValue(vstarUserKpiReport_needFollowUp.get(i).getStationNum());
            //客户状态
            sheet1Cell3 = titleRow2.createCell(7);
            if (i % 2 == 0) {
                sheet1Cell3.setCellStyle(bodyStyle);
            } else {
                sheet1Cell3.setCellStyle(bodyStyle2);
            }
            sheet1Cell3.setCellValue(vstarUserKpiReport_needFollowUp.get(i).getCustomerStat());
            //客户类型
            sheet1Cell3 = titleRow2.createCell(8);
            if (i % 2 == 0) {
                sheet1Cell3.setCellStyle(bodyStyle);
            } else {
                sheet1Cell3.setCellStyle(bodyStyle2);
            }
            sheet1Cell3.setCellValue(vstarUserKpiReport_needFollowUp.get(i).getCustomerType());
            //油漆品牌
            sheet1Cell3 = titleRow2.createCell(9);
            if (i % 2 == 0) {
                sheet1Cell3.setCellStyle(bodyStyle);
            } else {
                sheet1Cell3.setCellStyle(bodyStyle2);
            }
            sheet1Cell3.setCellValue(vstarUserKpiReport_needFollowUp.get(i).getPaintBrand());
            //品牌定位
            sheet1Cell3 = titleRow2.createCell(10);
            if (i % 2 == 0) {
                sheet1Cell3.setCellStyle(bodyStyle);
            } else {
                sheet1Cell3.setCellStyle(bodyStyle2);
            }
            sheet1Cell3.setCellValue(vstarUserKpiReport_needFollowUp.get(i).getProduct_position());
            //服务商
            sheet1Cell3 = titleRow2.createCell(11);
            if (i % 2 == 0) {
                sheet1Cell3.setCellStyle(bodyStyle);
            } else {
                sheet1Cell3.setCellStyle(bodyStyle2);
            }
            sheet1Cell3.setCellValue(vstarUserKpiReport_needFollowUp.get(i).getServiceCompany());
            //重点工作分类
            sheet1Cell3 = titleRow2.createCell(12);
            if (i % 2 == 0) {
                sheet1Cell3.setCellStyle(bodyStyle);
            } else {
                sheet1Cell3.setCellStyle(bodyStyle2);
            }
            sheet1Cell3.setCellValue(vstarUserKpiReport_needFollowUp.get(i).getWorkType());
            //服务自我评估
            sheet1Cell3 = titleRow2.createCell(13);
            if (i % 2 == 0) {
                sheet1Cell3.setCellStyle(bodyStyle);
            } else {
                sheet1Cell3.setCellStyle(bodyStyle2);
            }
            sheet1Cell3.setCellValue(vstarUserKpiReport_needFollowUp.get(i).getServicePoint());
            //异常情况
            sheet1Cell3 = titleRow2.createCell(14);
            if (i % 2 == 0) {
                sheet1Cell3.setCellStyle(bodyStyle);
            } else {
                sheet1Cell3.setCellStyle(bodyStyle2);
            }
            sheet1Cell3.setCellValue(vstarUserKpiReport_needFollowUp.get(i).getAbnormalSituation().get(0));

        }

        /**
         * 第3个sheet
         */
        XSSFSheet sheet2 = workbook.createSheet("计划报告");
        for (int i = 0; i < planTitle.length; i++) {
            sheet2.setColumnWidth(i, 30 * 256);
        }
        //创建标题
        XSSFRow titleRow3 = sheet2.createRow(0);
        XSSFCell cell3 = null;
        for (int i = 0; i < planTitle.length; i++) {
            cell3 = titleRow3.createCell(i);
            cell3.setCellValue(planTitle[i]);
            cell3.setCellStyle(titleStyle);
        }
        XSSFCell sheet1Cell4 = null;
        for (int i = 0; i < vstarUserKpiReport_Plan.size(); i++) {
            titleRow3 = sheet2.createRow(i + 1);

            //日期
            sheet1Cell4 = titleRow3.createCell(0);
            if (i % 2 == 0) {
                sheet1Cell4.setCellStyle(bodyStyle);
            } else {
                sheet1Cell4.setCellStyle(bodyStyle2);
            }
            sheet1Cell4.setCellValue(vstarUserKpiReport_Plan.get(i).getDateTime());
            //工作人员
            sheet1Cell4 = titleRow3.createCell(1);
            if (i % 2 == 0) {
                sheet1Cell4.setCellStyle(bodyStyle);
            } else {
                sheet1Cell4.setCellStyle(bodyStyle2);
            }
            sheet1Cell4.setCellValue(vstarUserKpiReport_Plan.get(i).getWorkUser());
            //所属省份
            sheet1Cell4 = titleRow3.createCell(2);
            if (i % 2 == 0) {
                sheet1Cell4.setCellStyle(bodyStyle);
            } else {
                sheet1Cell4.setCellStyle(bodyStyle2);
            }
            sheet1Cell4.setCellValue(vstarUserKpiReport_Plan.get(i).getProvice());
            //客户名称
            sheet1Cell4 = titleRow3.createCell(3);
            if (i % 2 == 0) {
                sheet1Cell4.setCellStyle(bodyStyle);
            } else {
                sheet1Cell4.setCellStyle(bodyStyle2);
            }
            sheet1Cell4.setCellValue(vstarUserKpiReport_Plan.get(i).getCustomerUser());
            //维修站类型
            sheet1Cell4 = titleRow3.createCell(4);
            if (i % 2 == 0) {
                sheet1Cell4.setCellStyle(bodyStyle);
            } else {
                sheet1Cell4.setCellStyle(bodyStyle2);
            }
            sheet1Cell4.setCellValue(vstarUserKpiReport_Plan.get(i).getCategory());
            //油漆品牌
            sheet1Cell4 = titleRow3.createCell(5);
            if (i % 2 == 0) {
                sheet1Cell4.setCellStyle(bodyStyle);
            } else {
                sheet1Cell4.setCellStyle(bodyStyle2);
            }
            sheet1Cell4.setCellValue(vstarUserKpiReport_Plan.get(i).getProduct_position());
            //客户代码
            sheet1Cell4 = titleRow3.createCell(6);
            if (i % 2 == 0) {
                sheet1Cell4.setCellStyle(bodyStyle);
            } else {
                sheet1Cell4.setCellStyle(bodyStyle2);
            }
            sheet1Cell4.setCellValue(vstarUserKpiReport_Plan.get(i).getStationNum());
            //客户状态
            sheet1Cell4 = titleRow3.createCell(7);
            if (i % 2 == 0) {
                sheet1Cell4.setCellStyle(bodyStyle);
            } else {
                sheet1Cell4.setCellStyle(bodyStyle2);
            }
            sheet1Cell4.setCellValue(vstarUserKpiReport_Plan.get(i).getCustomerStat());
            //客户类型
            sheet1Cell4 = titleRow3.createCell(8);
            if (i % 2 == 0) {
                sheet1Cell4.setCellStyle(bodyStyle);
            } else {
                sheet1Cell4.setCellStyle(bodyStyle2);
            }
            sheet1Cell4.setCellValue(vstarUserKpiReport_Plan.get(i).getCustomerType());
            //油漆品牌
            sheet1Cell4 = titleRow3.createCell(9);
            if (i % 2 == 0) {
                sheet1Cell4.setCellStyle(bodyStyle);
            } else {
                sheet1Cell4.setCellStyle(bodyStyle2);
            }
            sheet1Cell4.setCellValue(vstarUserKpiReport_Plan.get(i).getPaintBrand());
            //品牌定位
            sheet1Cell4 = titleRow3.createCell(10);
            if (i % 2 == 0) {
                sheet1Cell4.setCellStyle(bodyStyle);
            } else {
                sheet1Cell4.setCellStyle(bodyStyle2);
            }
            sheet1Cell4.setCellValue(vstarUserKpiReport_Plan.get(i).getProduct_position());
            //服务商
            sheet1Cell4 = titleRow3.createCell(11);
            if (i % 2 == 0) {
                sheet1Cell4.setCellStyle(bodyStyle);
            } else {
                sheet1Cell4.setCellStyle(bodyStyle2);
            }
            sheet1Cell4.setCellValue(vstarUserKpiReport_Plan.get(i).getServiceCompany());
            //工作内容
            sheet1Cell4 = titleRow3.createCell(12);
            if (i % 2 == 0) {
                sheet1Cell4.setCellStyle(bodyStyle);
            } else {
                sheet1Cell4.setCellStyle(bodyStyle2);
            }
            sheet1Cell4.setCellValue(vstarUserKpiReport_Plan.get(i).getWorkType());
            //服务自我评估
            sheet1Cell4 = titleRow3.createCell(13);
            if (i % 2 == 0) {
                sheet1Cell4.setCellStyle(bodyStyle);
            } else {
                sheet1Cell4.setCellStyle(bodyStyle2);
            }
            sheet1Cell4.setCellValue(vstarUserKpiReport_Plan.get(i).getServicePoint());
            //具体品牌
            sheet1Cell4 = titleRow3.createCell(14);
            if (i % 2 == 0) {
                sheet1Cell4.setCellStyle(bodyStyle);
            } else {
                sheet1Cell4.setCellStyle(bodyStyle2);
            }
            sheet1Cell4.setCellValue(vstarUserKpiReport_Plan.get(i).getBrand());
        }
        return workbook;
    }

}
